package cn.ctbu.xiaopeng.studentscoremanage.dao;


import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.StudentQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.Student;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.StudentPageVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface StudentRepository extends JpaRepository<Student,Integer> {


    /**
     * 通过将classNo和 departmentId将班级表和院系表连接起来，查询班级和所属院系信息
     * 使用 @Query 注解进行自定义查询，分页查询课程和教师信息并返回 CoursePageVO
     *
     * @param studentQueryDTO
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.StudentPageVO(" +
            "s.id, " +
            "s.sno, " +
            "s.classNo, " +
            "c.className, " +  // 从班级表获取班级名称
            "s.name, " +
            "s.sex, " +
            "s.age, " +
            "s.departmentId, " +
            "d.department, " +  // 从部门表获取部门名称
            "s.password, " +
            "s.phone, " +
            "s.email) " +
            "FROM tb_student s " +
            "LEFT JOIN tb_class c ON s.classNo = c.classNo " + // 连接班级表
            "LEFT JOIN tb_department d ON s.departmentId = d.departmentId " + // 连接部门表
            "WHERE (:#{#studentQueryDTO.name} IS NULL OR s.name LIKE %:#{#studentQueryDTO.name}%) " +
            "AND (:#{#studentQueryDTO.sno} IS NULL OR s.sno LIKE %:#{#studentQueryDTO.sno}%) " +
            "AND (:#{#studentQueryDTO.className} IS NULL OR c.className LIKE %:#{#studentQueryDTO.className}%) " +
            "AND (:#{#studentQueryDTO.sex} IS NULL OR s.sex = :#{#studentQueryDTO.sex}) " +
            "AND (:#{#studentQueryDTO.age} IS NULL OR s.age = :#{#studentQueryDTO.age}) " +
            "AND (:#{#studentQueryDTO.department} IS NULL OR d.department LIKE %:#{#studentQueryDTO.department}%)")
    Page<StudentPageVO> getStudentPageByConditions(@Param("studentQueryDTO") StudentQueryDTO studentQueryDTO, Pageable pageable);




    /**
     * 根据姓名模糊查询 (like)
     * @param name
     * @return
     */
    List<Student> findByNameLike(String name);

    /**
     * 根据id批量删除
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_student s WHERE s.id IN :ids")
    void deleteByIds(List<Integer> ids);

    /**
     * 根据姓名模糊查询 (startingWith)
     * @param name
     * @return
     */
    List<Student> findByNameStartingWith(String name);

    /**
     * 根据姓名和密码查询 - 登录
     * @param name
     * @param password
     * @return
     */
    List<Student> findByNameAndPassword(String name,String password);

    /**
     * 根据学号查询
     * @param sno
     * @return
     */
    Student findBySno(String sno);


}
